package com.navi.loaddata.loadService.impl.mesData.defectData;

import com.navi.loaddata.core.errorLog.ErrorCode;
import com.navi.loaddata.core.etlException.ETLException2;
import com.navi.loaddata.core.gpEntity.File.FileDefectPojo;
import com.navi.loaddata.core.mysqlEntity.EtlConfDModel;
import com.navi.loaddata.core.repository.impl.GreenPlumRepositoryImpl;
import com.navi.loaddata.core.repository.impl.MySqlRepositoryImpl;
import com.navi.loaddata.core.util.JdbcUtil;
import com.navi.loaddata.core.util.LoggerUtil;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.hibernate.Session;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@Service
public class LoadMESDefectInfoService {
    private static final Logger logger = LoggerFactory.getLogger(LoadMESDefectInfoService.class);
    private LoggerUtil loggerUtil = new LoggerUtil(this.getClass());
    @Autowired
    private GreenPlumRepositoryImpl gpDao;

    @Autowired
    private MySqlRepositoryImpl dao;

    public List<FileDefectPojo> getMESDefectInfo(EtlConfDModel etlConfD) throws Exception {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
        String startTime = sdf.format(etlConfD.getRunStartTimestamp().getTime());
        String endTime = sdf.format(etlConfD.getRunEndTimestamp().getTime());

        List<FileDefectPojo> defectList = new ArrayList<>();

        QueryRunner qr_mes_user = JdbcUtil.getQueryRunner_mes_user();
        try {
//            String sql = "SELECT TO_CHAR(B.EVENTTIME, 'YYYY-MM-DD HH24:MI:SS') EVTTIMESTAMP, B.PRODUCTNAME GLASSID, B.PROCESSOPERATIONNAME OPEID, B.DEFECTCODE defectcode, B.PRODUCTSPECNAME PRODID, B.LINENAME EQPTID, 'OC' AS SHOPID, '3' AS SHOPKEYFK, B.EVENTUSER EVTUSER, SUBSTR(B.COORDINATE, INSTR(B.COORDINATE,'=',1,1)+1, INSTR(B.COORDINATE,',',1,1)-INSTR(B.COORDINATE,'=',1,1)-1) GATENO, SUBSTR(B.COORDINATE, INSTR(B.COORDINATE,'=',1,2)+1, LENGTH(B.COORDINATE)-INSTR(B.COORDINATE,'=',1,2)) DATANO, B.PRODUCTNAME PANELID, ROW_NUMBER() OVER (PARTITION BY B.PRODUCTNAME, B.PROCESSOPERATIONNAME, SUBSTR(B.TIMEKEY,0,14) ORDER BY B.PRODUCTNAME) DEFECTNO" +
//                    " FROM P1MESADM.CT_PRODUCTDEFECTINFO B" +
//                    " WHERE B.TIMEKEY >= '" + startTime + "'" +
//                    " AND B.TIMEKEY < '" + endTime + "'" +
//                    " AND B.PROCESSOPERATIONNAME = '"+ etlConfD.getOpe() + "'" +
//                    " AND B.EVENTNAME = 'TrackOut'" +
//                    " ORDER BY B.PRODUCTNAME";
            String sql = "SELECT TO_CHAR(B.EVENTTIME, 'YYYY-MM-DD HH24:MI:SS') EVTTIMESTAMP, B.PRODUCTNAME GLASSID, B.PROCESSOPERATIONNAME OPEID, B.DEFECTCODE defectcode, B.PRODUCTSPECNAME PRODID, B.LINENAME EQPTID, 'OC' AS SHOPID, '3' AS SHOPKEYFK, B.EVENTUSER EVTUSER, SUBSTR(B.COORDINATE, INSTR(B.COORDINATE,'=',1,1)+1, INSTR(B.COORDINATE,',',1,1)-INSTR(B.COORDINATE,'=',1,1)-1) GATENO, SUBSTR(B.COORDINATE, INSTR(B.COORDINATE,'=',1,2)+1, LENGTH(B.COORDINATE)-INSTR(B.COORDINATE,'=',1,2)) DATANO, B.PRODUCTNAME PANELID, A.REWORKCOUNT AS JGECNT, TO_CHAR(A.EVENTTIME, 'YYYY-MM-DD HH24:MI:SS') AS PROCENDTIME, ROW_NUMBER() OVER (PARTITION BY B.PRODUCTNAME, B.PROCESSOPERATIONNAME, SUBSTR(B.TIMEKEY,0,14) ORDER BY B.PRODUCTNAME) DEFECTNO" +
                    " FROM P1MESADM.PRODUCTHISTORY A, P1MESADM.CT_PRODUCTDEFECTINFO B" +
                    " WHERE A.EVENTTIME = B.EVENTTIME" +
                    " AND A.PRODUCTNAME = B.PRODUCTNAME" +
                    " AND A.OLDPROCESSOPERATIONNAME = B.PROCESSOPERATIONNAME" +
                    " AND A.EVENTNAME = B.EVENTNAME" +
                    " AND A.TIMEKEY >= '" + startTime + "'" +
                    " AND A.TIMEKEY < '" + endTime + "'" +
                    " AND B.TIMEKEY >= '" + startTime + "'" +
                    " AND B.TIMEKEY < '" + endTime + "'" +
                    " AND B.PROCESSOPERATIONNAME = '"+ etlConfD.getOpe() + "'" +
                    " AND B.EVENTNAME = 'TrackOut'" +
                    " ORDER BY B.PRODUCTNAME";


            logger.info("查询Defect信息的SQL：" + sql);
            defectList = qr_mes_user.query(sql, new BeanListHandler<FileDefectPojo>(FileDefectPojo.class));
            logger.info("查询Defect信息数据行：" + defectList.size());
        } catch (Exception e) {
            logger.error("报错信息：" + LoggerUtil.stackTraceToString(e));
            throw new ETLException2(ErrorCode.ERROR_GET_MES_DEFECT_INFO, e);
        }
        return defectList;
    }


    public void clearObject(FileDefectPojo defectInfo) {
        Session session = gpDao.getEntityManagerSession();
        session.evict(defectInfo);//防止HIBERNATE自动保存实体
    }

    @Transactional(transactionManager = "primaryPlatformTransactionManager")
    public void updateEtlConfD(EtlConfDModel etlConfD) {
        dao.update(etlConfD);
    }
}
